package com.iris.live.services.data.repositories;

import com.iris.live.services.data.models.GroupAreaModel;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * Created by yitianle on 2017/8/11.
 */
public interface GroupAreaRepository extends JpaRepository<GroupAreaModel, Integer> {

    @Query("select a from GroupAreaModel a where a.parentId=?1 ")
    List<GroupAreaModel> getAreaListByParentId(Integer parentId);

    @Query("select a from GroupAreaModel a where a.parentId=?1 or a.parentId=?2")
    List<GroupAreaModel> getAreaListByParentIds(Integer parentId,Integer groupId);

    @Query("select a.id from GroupAreaModel a where a.permit=?1 ")
    Integer getIdByPermit(String permit);

    @Query("select a from GroupAreaModel a where a.permit=?1 ")
    GroupAreaModel getInfoByPermit(String permit);

    @Query("select a from GroupAreaModel a where a.id=?1 ")
    GroupAreaModel getParentInfoById(Integer id);

    @Query(value = "select a.dealer_name from group_area a join group_area b on a.id = b.parent_id where b.permit = ?1 and a.parent_id = 1 and b.status = 1",nativeQuery = true)
    String  getParentName(String permit);

    @Query(value = "select a from GroupAreaModel a where a.isDealer=1 and a.status =1")
    List<GroupAreaModel>  getAllDealer();

//    @Query(value = "select * from group_area a where brand like ?1 and parent_id = (select id from group_area where permit = ?2)",nativeQuery = true)
    @Query(value = "SELECT * FROM group_area WHERE id IN(SELECT parent_id FROM group_area a WHERE brand LIKE ?1) AND parent_id IN (SELECT id FROM group_area WHERE permit = ?2)",nativeQuery = true)
    List<GroupAreaModel> getByBrandLikeAndPermit(String brand,String permit);

    @Query(value = "select parent_id from group_area where id = ?1 ",nativeQuery = true)
    int getParentIdById(Integer id );

    @Query(value = "select parent_id from group_area where permit = ?1 ",nativeQuery = true)
    int getParentIdByPermit(String permit );


    @Query(value = "select b.permit from group_area a left join group_area b on a.parent_id = b.id where  a.permit = ?1 and b.status = 1 and b.permit !='LSH'",nativeQuery = true)
    String getParentPermitByPermit (String permit);

    @Query(value = "select distinct a.* from group_area a,group_area b where a.id = b.parent_id and b.brand like ?1",nativeQuery = true)
    List<GroupAreaModel> getAreaByBrand(String brand);

    @Query("select a from GroupAreaModel a where a.parentId = 1")
    List<GroupAreaModel> getAllBrandAndarea();
}
